{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 原创：周志鹏\n",
    "### 公众号：数据不吹牛，更多案例和有趣分析等你来撩"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import time\n",
    "import pandas as pd\n",
    "\n",
    "os.chdir('C:\\\\Users\\\\Administrator\\\\Desktop\\\\data')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 打开单个表格"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>日期</th>\n",
       "      <th>转化率</th>\n",
       "      <th>访客数</th>\n",
       "      <th>三级类目</th>\n",
       "      <th>客单价</th>\n",
       "      <th>品牌</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2019-08</td>\n",
       "      <td>0.025806</td>\n",
       "      <td>221402</td>\n",
       "      <td>绑钩器</td>\n",
       "      <td>33.284283</td>\n",
       "      <td>品牌-17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2019-08</td>\n",
       "      <td>0.019638</td>\n",
       "      <td>14074</td>\n",
       "      <td>绑钩器</td>\n",
       "      <td>233.995330</td>\n",
       "      <td>品牌-12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2019-08</td>\n",
       "      <td>0.065407</td>\n",
       "      <td>75392</td>\n",
       "      <td>绑钩器</td>\n",
       "      <td>11.938785</td>\n",
       "      <td>品牌-20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2019-08</td>\n",
       "      <td>0.015905</td>\n",
       "      <td>85529</td>\n",
       "      <td>绑钩器</td>\n",
       "      <td>41.059966</td>\n",
       "      <td>品牌-13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2019-08</td>\n",
       "      <td>0.039033</td>\n",
       "      <td>23839</td>\n",
       "      <td>绑钩器</td>\n",
       "      <td>44.502008</td>\n",
       "      <td>品牌-1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        日期       转化率     访客数 三级类目         客单价     品牌\n",
       "0  2019-08  0.025806  221402  绑钩器   33.284283  品牌-17\n",
       "1  2019-08  0.019638   14074  绑钩器  233.995330  品牌-12\n",
       "2  2019-08  0.065407   75392  绑钩器   11.938785  品牌-20\n",
       "3  2019-08  0.015905   85529  绑钩器   41.059966  品牌-13\n",
       "4  2019-08  0.039033   23839  绑钩器   44.502008   品牌-1"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "name = '垂钓装备&绑钩器.xlsx'\n",
    "df = pd.read_excel(name)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 查看日期范围"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['2019-08', '2019-07', '2019-06', '2019-05', '2019-04', '2019-03',\n",
       "       '2019-02', '2019-01', '2018-12', '2018-11', '2018-10', '2018-09'], dtype=object)"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['日期'].unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 计算销售额"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>日期</th>\n",
       "      <th>转化率</th>\n",
       "      <th>访客数</th>\n",
       "      <th>三级类目</th>\n",
       "      <th>客单价</th>\n",
       "      <th>品牌</th>\n",
       "      <th>销售额</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2019-08</td>\n",
       "      <td>0.025806</td>\n",
       "      <td>221402</td>\n",
       "      <td>绑钩器</td>\n",
       "      <td>33.284283</td>\n",
       "      <td>品牌-17</td>\n",
       "      <td>190167.455681</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2019-08</td>\n",
       "      <td>0.019638</td>\n",
       "      <td>14074</td>\n",
       "      <td>绑钩器</td>\n",
       "      <td>233.995330</td>\n",
       "      <td>品牌-12</td>\n",
       "      <td>64673.807815</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2019-08</td>\n",
       "      <td>0.065407</td>\n",
       "      <td>75392</td>\n",
       "      <td>绑钩器</td>\n",
       "      <td>11.938785</td>\n",
       "      <td>品牌-20</td>\n",
       "      <td>58871.997672</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2019-08</td>\n",
       "      <td>0.015905</td>\n",
       "      <td>85529</td>\n",
       "      <td>绑钩器</td>\n",
       "      <td>41.059966</td>\n",
       "      <td>品牌-13</td>\n",
       "      <td>55856.842507</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2019-08</td>\n",
       "      <td>0.039033</td>\n",
       "      <td>23839</td>\n",
       "      <td>绑钩器</td>\n",
       "      <td>44.502008</td>\n",
       "      <td>品牌-1</td>\n",
       "      <td>41409.600947</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        日期       转化率     访客数 三级类目         客单价     品牌            销售额\n",
       "0  2019-08  0.025806  221402  绑钩器   33.284283  品牌-17  190167.455681\n",
       "1  2019-08  0.019638   14074  绑钩器  233.995330  品牌-12   64673.807815\n",
       "2  2019-08  0.065407   75392  绑钩器   11.938785  品牌-20   58871.997672\n",
       "3  2019-08  0.015905   85529  绑钩器   41.059966  品牌-13   55856.842507\n",
       "4  2019-08  0.039033   23839  绑钩器   44.502008   品牌-1   41409.600947"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['销售额'] = df['访客数'] * df['转化率'] * df['客单价']\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 单表销售额合并"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>品牌</th>\n",
       "      <th>销售额</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>品牌-1</td>\n",
       "      <td>529837.745358</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>品牌-10</td>\n",
       "      <td>217976.661847</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>品牌-11</td>\n",
       "      <td>327093.079507</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>品牌-12</td>\n",
       "      <td>485635.295843</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>品牌-13</td>\n",
       "      <td>438391.195855</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      品牌            销售额\n",
       "0   品牌-1  529837.745358\n",
       "1  品牌-10  217976.661847\n",
       "2  品牌-11  327093.079507\n",
       "3  品牌-12  485635.295843\n",
       "4  品牌-13  438391.195855"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_sum = df.groupby('品牌')['销售额'].sum().reset_index()\n",
    "df_sum.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 增加行业标签"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>品牌</th>\n",
       "      <th>销售额</th>\n",
       "      <th>行业</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>品牌-1</td>\n",
       "      <td>529837.745358</td>\n",
       "      <td>垂钓装备&amp;绑钩器</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>品牌-10</td>\n",
       "      <td>217976.661847</td>\n",
       "      <td>垂钓装备&amp;绑钩器</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>品牌-11</td>\n",
       "      <td>327093.079507</td>\n",
       "      <td>垂钓装备&amp;绑钩器</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>品牌-12</td>\n",
       "      <td>485635.295843</td>\n",
       "      <td>垂钓装备&amp;绑钩器</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>品牌-13</td>\n",
       "      <td>438391.195855</td>\n",
       "      <td>垂钓装备&amp;绑钩器</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      品牌            销售额        行业\n",
       "0   品牌-1  529837.745358  垂钓装备&绑钩器\n",
       "1  品牌-10  217976.661847  垂钓装备&绑钩器\n",
       "2  品牌-11  327093.079507  垂钓装备&绑钩器\n",
       "3  品牌-12  485635.295843  垂钓装备&绑钩器\n",
       "4  品牌-13  438391.195855  垂钓装备&绑钩器"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_sum['行业'] = name.replace('.xlsx','')\n",
    "df_sum.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 搞定单个文件，批量处理只需要循环即可"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "用Python操作所花费时间：2.6550002098083496 s\n"
     ]
    }
   ],
   "source": [
    "import time\n",
    "\n",
    "#开始时间\n",
    "start = time.time()\n",
    "\n",
    "#存储汇总的结果\n",
    "result = pd.DataFrame()\n",
    "\n",
    "#循环遍历表格名称\n",
    "for name in os.listdir():\n",
    "    df = pd.read_excel(name)\n",
    "    #计算销售额字段\n",
    "    df['销售额'] = df['访客数'] * df['转化率'] * df['客单价']\n",
    "    #按品牌对细分行业销售额进行汇总\n",
    "    df_sum = df.groupby('品牌')['销售额'].sum().reset_index()\n",
    "    df_sum['类目'] = name.replace('.xlsx','')\n",
    "    result = pd.concat([result,df_sum])\n",
    "\n",
    "#对最终结果按销售额进行排序\n",
    "final = result.groupby('品牌')['销售额'].sum().reset_index().sort_values('销售额',ascending = False)\n",
    "\n",
    "#结束时间\n",
    "end = time.time()\n",
    "print('用Python操作所花费时间：{} s'.format(end-start))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>品牌</th>\n",
       "      <th>销售额</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>品牌-5</td>\n",
       "      <td>1.226224e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>品牌-17</td>\n",
       "      <td>1.195281e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>品牌-11</td>\n",
       "      <td>1.151829e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>品牌-13</td>\n",
       "      <td>1.150687e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>品牌-12</td>\n",
       "      <td>1.143520e+09</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       品牌           销售额\n",
       "15   品牌-5  1.226224e+09\n",
       "8   品牌-17  1.195281e+09\n",
       "2   品牌-11  1.151829e+09\n",
       "4   品牌-13  1.150687e+09\n",
       "3   品牌-12  1.143520e+09"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 不显示科学计数法，保留小数点两位数"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>品牌</th>\n",
       "      <th>销售额</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>品牌-5</td>\n",
       "      <td>1226223640.73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>品牌-17</td>\n",
       "      <td>1195280571.60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>品牌-11</td>\n",
       "      <td>1151829215.73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>品牌-13</td>\n",
       "      <td>1150687029.66</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>品牌-12</td>\n",
       "      <td>1143519788.23</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       品牌           销售额\n",
       "15   品牌-5 1226223640.73\n",
       "8   品牌-17 1195280571.60\n",
       "2   品牌-11 1151829215.73\n",
       "4   品牌-13 1150687029.66\n",
       "3   品牌-12 1143519788.23"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.set_option('display.float_format', lambda x: '%.2f' % x)\n",
    "final.head()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.5.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
